package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import Entity.Reference;


public class ServiceReferenceDAO {
	private String DATABASE_NAME="MockProject";
	private String TABLE_NAME="ReferenceData";
	private String USERNAME="sa";
	private String PASSWORD="12345678";
	
	private ArrayList<Reference> listR;
	
	public ArrayList<Reference> getListReference (String []refCode){
			Connection con = null;	
			String sql="SELECT RefID,RefCode,RefValue "+
						"FROM "+TABLE_NAME+" " +
						"WHERE ";
			int i;
			for(i=0;i<refCode.length-1;i++){
				sql+=" RefCode= '"+refCode[i]+"' OR ";
			}
			sql+="RefCode= '"+refCode[i]+"'";
	        
	        System.out.println("sql= "+sql);
	        try{
	        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
	        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
	        							"database="+DATABASE_NAME+";" + 
	        							"user="+USERNAME+";" + 
	        							"password="+PASSWORD+";"; 
	        	con = DriverManager.getConnection(connectionUrl); 
	        	Statement stmt = con.createStatement();  
	        	ResultSet rs=stmt.executeQuery(sql);
	        	int count=0;
	        	listR=new ArrayList<Reference>();
	        	while(rs.next()){
	        		count++;
	        		String refID=rs.getString(1);
	        		String refCodes=rs.getString(2);
	        		String refValue=rs.getString(3);
	        		
	        		System.out.println("ID="+refID+" value="+refValue);
	        		listR.add(new Reference(refID, refCodes, refValue));
	        	}
	        	
	        	return listR;
	        }
	        catch(Exception e){
	        	e.printStackTrace();
	        }
	        finally {
	            if (con != null) {
	               try {
	                  con.close();
	               } catch (Exception e) {
	            	   e.printStackTrace();
	               }
	            }
	         }
	            
	    	return null;
	}
	
	public ArrayList<String> getServiceReference (int serviceID){
		Connection con = null;	
		String sql="SELECT RefID "+
					"FROM ServiceReference " +
					"WHERE serviceID= "+serviceID;
		
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
        							"database="+DATABASE_NAME+";" + 
        							"user="+USERNAME+";" + 
        							"password="+PASSWORD+";"; 
        	con = DriverManager.getConnection(connectionUrl); 
        	Statement stmt = con.createStatement();  
        	ResultSet rs=stmt.executeQuery(sql);
        	
        	ArrayList<String> refID=new ArrayList<String>();
        	while(rs.next()){	
        		String refIDs=rs.getString(1);
        		System.out.println("refID:"+refIDs);
        		refID.add(refIDs);
        	}
        	return refID;
        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
            
    	return null;
    }
	
	public void saveServiceReference(int serviceID, String[] refID){
		Connection con = null;	
		
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        	String connectionUrl ="jdbc:sqlserver://localhost:1130;" + 
        							"database="+DATABASE_NAME+";" + 
        							"user="+USERNAME+";" + 
        							"password="+PASSWORD+";"; 
        	con = DriverManager.getConnection(connectionUrl); 
        	Statement stmt = con.createStatement();
        	
        	String sqlDel="DELETE FROM ServiceReference WHERE serviceID="+serviceID;
        	stmt.executeUpdate(sqlDel);
        	
        	stmt = con.createStatement();
        	String sql="";
        	for(int i=0;i<refID.length;i++){
        		sql="INSERT INTO ServiceReference (ServiceID,RefID) VALUES (" +
        				serviceID+",'" +refID[i]+
        				"')";
        		stmt.executeUpdate(sql);
        	}

        }
        catch(Exception e){
        	e.printStackTrace();
        }
        finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
            	   e.printStackTrace();
               }
            }
         }
           
    }
	
	
}
